package dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.impl.NewPooledConnection;

import model.Goods;
import model.Recommend;
import utils.DataSourceUtils;

public class GoodsDao {
	
	
	public	List<Map<String,Object>> getGoodsList(int recommendType) throws SQLException {
		
		QueryRunner	r =   new  QueryRunner(DataSourceUtils.getDataSource());
		String	sql= "SELECT g.id,g.name,g.cover,g.price,t.name typename FROM banner  r,ware g,type  t WHERE type=? AND r.goods_id=g.id AND g.type_id=t.id";
		return r.query(sql, new MapListHandler(),recommendType);
	}

public	Map<String,Object> getScrollGoods() throws SQLException {
		
		QueryRunner	r =   new  QueryRunner(DataSourceUtils.getDataSource());
		String	sql= "SELECT g.id,g.name,g.cover,g.price FROM banner  r,ware g WHERE type=1 AND r.goods_id=g.id ";
		return r.query(sql, new MapHandler());
	}

	public	List<Goods>	selectGoods(int  typeId,int pageNo,int pageSize) throws SQLException{
		
		QueryRunner  r  = new QueryRunner(DataSourceUtils.getDataSource());
		if(typeId==0) {
			
			String	sql		= "select  *  from  ware limit  ?,?";
			return   r.query(sql,new BeanListHandler<Goods>(Goods.class), (pageNo-1)*pageSize,pageSize);
		}else {
			String	sql		= "select  *  from  ware where  type_id=? limit  ?,?";
		     return	r.query(sql,new BeanListHandler<Goods>(Goods.class),typeId, (pageNo-1)*pageSize,pageSize);
		}
	}
	
	public	int		getGoodsCount(int typeId) throws SQLException {
		
	QueryRunner  r  = new  QueryRunner(DataSourceUtils.getDataSource());
	
	String	sql =  "";
	if(typeId==0) {
			sql  =  "select  count(*)  from  ware";
			return  r.query(sql, new ScalarHandler<Long>()).intValue();
	 
	}else {
		
		  sql  =  "select   count(*)  from  ware  where  type_id=?";
		  return  r.query(sql, new ScalarHandler<Long>(),typeId).intValue();
		
	}
	
		
	}
	
	public	List<Goods>  selectGoodsRecommend(int type,int pageNo,int pageSize) throws SQLException{
		
		QueryRunner   r  = new QueryRunner(DataSourceUtils.getDataSource());
		if(type==0) {
			
			//当不添加推荐类型限制的时候
			String  sql = "select g.id,g.name,g.cover,g.image1,g.image2,g.intro,g.price,g.stock,t.name typename from ware g,type t where g.type_id=t.id  limit ?,?";
			return	r.query(sql, new BeanListHandler<Goods>(Goods.class),(pageNo-1)*pageSize,pageSize);
			
			
		}
		
		String  sql = "select  g.id,g.name,g.cover,g.image1,g.image2,g.intro,g.price,g.stock,t.name typename  from  ware g,banner r,type t where g.id=r.goods_id  and g.type_id=t.id and r.type=?  limit ?,?";
		return	r.query(sql, new BeanListHandler<Goods>(Goods.class),type,(pageNo-1)*pageSize,pageSize);
		
	}
	
	public	int  selectGoodsRecommendCount(int type) throws SQLException{
		
		if(type==0)return getGoodsCount(0);
		QueryRunner   r  = new QueryRunner(DataSourceUtils.getDataSource());
		String  sql = "select  count(*)  from  banner  where type=?";
		return	r.query(sql, new  ScalarHandler<Long>(),type).intValue();
		
	}

	public	Goods	getById(int id) throws SQLException {
		
		QueryRunner  r  =  new  QueryRunner(DataSourceUtils.getDataSource());
		String  sql  =  "select  g.id,g.name,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,t.id typeid,t.name typename  from  ware g,type t  where  g.id = ?   and   g.type_id=t.id";
		return   r.query(sql, new BeanHandler<Goods>(Goods.class),id);
		
	}

	public int getSearchCount(String keyword) throws SQLException {
		
		QueryRunner   r  = new QueryRunner(DataSourceUtils.getDataSource());
		String  sql = "select  count(*)  from  ware  where name like  ?";  
		return	r.query(sql, new  ScalarHandler<Long>(),"%"+keyword+"%").intValue();
	}

	public List<Goods> selectSearchGoods(String keyword, int pageNo, int pageSize) throws SQLException {
		
		QueryRunner   r  = new QueryRunner(DataSourceUtils.getDataSource());
		String  sql = "select  *  from  ware  where name like  ? limit ?,?";
		return	r.query(sql, new BeanListHandler<Goods>(Goods.class),"%"+keyword+"%",(pageNo-1)*pageSize,pageSize);
	}
	
	public boolean isScroll(Goods g) throws SQLException {
		
		return isRecommend(g, 1);
	}
	
	public boolean  isHot(Goods g) throws SQLException {
		
		return isRecommend(g, 2);
	}
	
	public boolean  isNew(Goods g) throws SQLException {
		
		return isRecommend(g, 3);
	}
	
	
	private boolean isRecommend(Goods g,int type) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from banner where type=? and goods_id=?";
		Recommend recommend = r.query(sql, new BeanHandler<Recommend>(Recommend.class),type,g.getId());
		if(recommend==null) {
			return false;
		}else {
			return true;
		}
	}
		// 添加商品
	public void addRecommend(int id,int type) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into banner(type,goods_id) values(?,?)";
		r.update(sql,type,id);
		
	}
	
		// 移除商品
	public void removeRecommend(int id,int type) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from banner where type=? and goods_id=?";
		r.update(sql,type,id);
		
	}
	public void insert(Goods g) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into ware(name,cover,image1,image2,price,intro,stock,type_id) values(?,?,?,?,?,?,?,?)";
		r.update(sql,g.getName(),g.getCover(),g.getImage1(),g.getImage2(),g.getPrice(),g.getIntro(),g.getStock(),g.getType().getId());
		
	}
	public void update(Goods g) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update ware set name=?,cover=?,image1=?,image2=?,price=?,intro=?,stock=?,type_id=? where id=?";  // 编辑商品
		r.update(sql,g.getName(),g.getCover(),g.getImage1(),g.getImage2(),g.getPrice(),g.getIntro(),g.getStock(),g.getType().getId(),g.getId());
	}
	public void delete(int id) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from ware where id=?";  // 删除商品
		r.update(sql,id);
	}
}
